<html>
<head>
<title>The Query Details Menu</title>
<link rel="stylesheet" type="text/css" href="../media/style.css">
</head>
<body>

<table border="0" cellspacing="0" cellpadding="0" height="48" width="100%">
  <tr>
    <td width="100%">
			<table width="100%" height="78" border="0" align="left" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" id="topnav">
			<tr>
				<!-- Header Side -->
				<td width="17">
					<img height="78" src="media/reportico100.png"/>
				</td>
				<td align="right"></td>
				<td align="center">
					<table width="100%" height="60" border="0" align="left" cellpadding="0" cellspacing="0">
						<tr>
							<td valign="top" align="left">
							<img src="media/repbanner.png"/></td>
							</td>
						</tr>
						<tr>
							<td align="center" valign="bottom" class="topmenucell">
								<nobr>
								&nbsp;
								</nobr>
							</td>
						</tr>
					</table>
				</td>
				<td width="103" class="toplogincell">
					&nbsp;
				</td>
			</tr>
			</table>
</td>
  </tr>
  <!--tr>
    <td class="header_top">reportico</td>
  </tr-->
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
  <tr>
    <td class="header_menu">
  		  [ <a href="../classtrees_reportico.html" class="menu">class tree: reportico</a> ]
		  [ <a href="../elementindex_reportico.html" class="menu">index: reportico</a> ]
		  [ <a href="../elementindex.html" class="menu">all elements</a> ]
    </td>
  </tr>
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
</table>

<table width="100%" border="0" cellpadding="0" cellspacing="0">
  <tr valign="top">
    <td width="200" class="menu">
      <b>Packages:</b><br />
              <a href="../li_reportico.html">reportico</a><br />
            <br /><br />
		<b>Tutorials/Manuals:</b><br />
					<strong>Package-level:</strong>
							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.pkg.html">Reportico Tutorial And User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.manual.pkg.html">Reportico User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.quickstart.pkg.html">Reportico Installation</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.gettingstarted.pkg.html">Getting Started</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.structure.pkg.html">Reportico Installation Structure</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.using.pkg.html">Configuring Reportico - System Parameters</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.embedding.pkg.html">Embedding Reportico</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.language.pkg.html">Language Support</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.projects.pkg.html">Projects</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.importlink.pkg.html">Importing and linking from reports</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.menus.pkg.html">Report Menus</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.prepare.pkg.html">Report Preparation and Execution</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.design.pkg.html">Report Design Mode</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.format.pkg.html">The Design Format Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.customcode.pkg.html">Custom Source Code</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.qrydet.pkg.html">The Query Details Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.assign.pkg.html">The Assignments Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.criteria.pkg.html">The Criteria Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.output.pkg.html">The Output Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.webservices.pkg.html">Turning Reports into Web Services (currently unavailable)</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.faq.pkg.html">Reportico FAQ</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.howto.pkg.html">Reportico Tutorial</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1.pkg.html">Tutorial No.1 - The Film Listing Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_1.pkg.html">Tutorial 1 Stage 1 - Creating a basic report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_2.pkg.html">Tutorial 1 Stage 2 - Creating User Entry Criteria</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_3.pkg.html">Tutorial 1 Stage 3 - Working with expressions using assignments</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_4.pkg.html">Tutorial 1 Stage 4 - Working with groups</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_5.pkg.html">Tutorial 1 Stage 5 - Including database graphics into the report and generating drilldowns</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial2_1.pkg.html">Tutorial 2 - Creating the loan history report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial3_1.pkg.html">Tutorial 3 - The Monthly Returns Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial4_1.pkg.html">Tutorial 4 - The Late Returns Summary report</a>
</ul>
</li></ul>
</li></ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix1.pkg.html">Appendix 1 - Reportico Project Configuration parameters</a>
</ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix2.pkg.html">Appendix 2 - Reportico URL request parameters</a>
</ul>

									                        <b>Files:</b><br />
      	  <div class="package">
			<a href="../reportico/_reportico.php.html">		reportico.php
		</a><br>
	  </div><br />
      
                </td>
    <td>
      <table cellpadding="10" cellspacing="0" width="100%" border="0"><tr><td valign="top">

<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="10%" align="left" valign="bottom"><a href=
"../reportico/tutorial_reportico.customcode.pkg.html">Prev</a></td>
<td width="80%" align="center" valign="bottom"></td>
<td width="10%" align="right" valign="bottom"><a href=
"../reportico/tutorial_reportico.assign.pkg.html">Next</a></td>
</tr>
</table>
<span><a name="appendix1 config.php.qrydet"></a><h2 class="title">The Query Details Menu</h2><a name="qrydet.overview"></a><h3 class="title">Query Details Menu Overview</h3><hr />

  <a name="qrydet."></a><h3 class="title">Query Details =&gt; SQL Tab</h3><a name="qrydet.QuerySql"></a><h4 class="title">SQL Query</h4><p>The SQL query text that retrieves the main report data. There is
      nothing to stop you entering statements that comprise outer joins,
      groups, complex expressions ( if supported by the appropriate SQL engine
      ).</p>

      <p>The entered query must comprise at minimum a SELECT clause and a
      WHERE clause. An ORDER BY clause is also necessary.</p>

      <p>The SELECT clause must not contain the star(*) notation. This is
      due to the way Reportico parses the SQL. Therefore explicit column names
      must be provided. Each column specified is stored in the report
      definition identified with a name. By default this is the column name
      itself, however a more appropriate name can be provided as a column
      alias. Also, if expression columns are specified then these should be
      provided with column aliases otherwise Reportico will automatically name
      them in the form <strong>column<strong>x</strong></strong> where
      x is an incremental number. It is sensible to provide meaningful names
      in this instance. The column names are important as they can be referred
      to elsewhere in the report when using the columns within for example
      groups, graphs and assignments.</p>

      <p>For example, the query</p>

      <p><code><pre>
    SELECT customer_id,
    customer_name, count(*) order_count FROM order WHERE 1 = 1 ORDER BY
    customer_id GROUP BY 1, 2
</pre></code></p>

      <p>will produce report output with column titles &quot;Id&quot;, &quot;Customer Name&quot; and &quot;Order Count&quot;.</p>

      <p>Now we have 3 report columns customer_id, name and order_count
      which can be referred to elsewhere in the report. Note also that the
      ORDER BY CLAUSE gets expanded to the appropriate order type.</p>

      <p>If you are providing a user from outside of reportico using the <strong>external_user</strong> then you can use this in your queries to filter by user by writing something like :-</p>

      <p><code><pre>
SELECT col1, col2
    FROM table1 
    WHERE user = {FRAMEWORK_USER}
</pre></code></p>
      <p>when you try to do this you will get a syntax error, but ignore the error and it will be replaced by the user id at report run time.</p><br />

    <a name="qrydet.SQLLimitFirst"></a><h4 class="title">Limit/First</h4><p>Causes the report to only output the specified number of rows</p><br />
    <a name="qrydet.SQLSkipOffset"></a><h4 class="title">Skip/Offset</h4><p>Causes report output to start from the record number specified here</p><br />


    <a name="qrydet.linkcrit"></a><h4 class="title">Linking in Criteria Values</h4><p>Additions can be made to the entered SQL Query that will cause
      entered user criteria to be linked in at report generation time.
      Normally this linking in is configured within the criteria item itself
      by filing in the <strong>Query Table Name</strong> and
      <strong>Query Column Name</strong> fields in the relevant criteria
      section. However, it is useful here where the way the criteria links in
      is not so simple.</p>

      <p>To link a criteria item in, place the where clause between square
      brackets. If the user did not specify any values for the criteria item
      then the clause is not included in the main report query since in
      specifying no values for a criteria this indicates not to filter on this
      criteria item at all.</p>

      <p>An example SQL statement may look as follows :-</p>

      <p><code><pre>
SELECT customer_name, order_no, order_date 
FROM customer, orders 
WHERE 1 = 1 
AND customer.customer_id = order.customer_id 
[AND customer.country IN ( {country} )] 
[AND order_date BETWEEN {date,RANGE1} AND {date,RANGE2} ]
ORDER BY order_date
</pre></code></p>

      <p>In the above example we are assuming a criteria item country
      exists that presumably allows selection of a set of country codes. Also
      a criteria item of date exists which is defined as a <strong>Date Range</strong>
      criteria.</p>

      <p>At run time, if the user has selected some countries then the
      {country} clause is replaced with the comma delimited values which
      relate to the <strong>Return Column</strong> parameter as entered
      against the {country} criteria item. These will probably relate to a
      country code column within a country table. See the criteria section for
      more details.</p>

      <p>The {date} criteria item has been defined as a date range criteria
      entry type since we are able to extract the user's entered start and end
      dates by using the <strong>RANGE1</strong> and <strong>RANGE2</strong> keywords. The format of the dates
      entered here relate to the Reportico configuration constant
      <strong>SW_DB_DATEFORMAT</strong>. See <a href="../reportico/tutorial_reportico.appendix1.pkg.html">Appendix 1 - Reportico Project Configuration parameters</a> for more details.</p>

      <p>Note that when you use the {} notation to link in a criteria item, the value is always replaced by the criterai value with quotes added. Sometimes
you do not want quotes added. In which case you need to add a final parameter of <strong>false</strong> to turn off automatic quoting.
An example use of this is when you want to allow the user to enter a match string to include in a LIKE or MATCHES statement. If the user wants to match all people with Smith in their name then the user should be able to just enter Smith. To handle this the cluase would look something like :-
      <code><pre>
[AND name LIKE &quot;%{matchcriteria,false}%&quot; ]
</pre></code></p><br /><hr />

  <a name="qrydet."></a><h3 class="title">Query Details =&gt; Query Columns Tab</h3><p>The Query Columns tab presents a list of all the report columns that
    are fetched within the query and those that have been manually added that
    will contain calculations as set up under the Assignments tab. Selecting
    any of these column presents the column parameter screen where you can set
    up the column display parameters.</p>

    <a name="qrydet.add"></a><h4 class="title">Add</h4><p>This Button creates a new manual column. This will not form part
      of the main query but acts as a place holder for a calculation value set
	      up in the Assignments tab. After adding a column it will appear named
	      <strong>New Column</strong> in the column list. You must change its
	      name by clicking on this button and altering the
	      <strong>Name</strong> value.</p><br />

	    <a name="qrydet.qcolparam"></a><h4 class="title">Query Column Parameters</h4><p>Column parameters can be changed by clicking the relevant column
	      button. This presents the options below :-</p><br />

	    <a name="qrydet.column_display"></a><h4 class="title">Show or Hide?</h4><p>Indicates whether this column should appear in the main report
	      body. Since reports are tabular, you may wish a particular column not to
	      appear because it will appear only in a group header or is used for a
	      further calculation. Or maybe you want it selected but just not shown at
	      this moment. To prevent a column showing select the
	      <strong>hide</strong> option. By default, this attribute is set to
	      <strong>show</strong>.</p><br />

	    <a name="qrydet.content_type"></a><h4 class="title">Content Type (PDF)</h4><p>By default this is set to plain. However if the column represents
	      a graphic selected from the database then this must be set to graphic
	      for it to appear correctly in PDF reports.</p><br />

	    <a name="qrydet.ColumnStartPDF"></a><h4 class="title">Column Start (PDF)</h4><p>Indicates the horizontal position of the column on PDF reports
	      only. As with other measurements, the value may be specified in cm, mm
	      or if no measurement is specified then points will be used.</p>

	      <p>If left blank then the column is automatically placed.</p><br />

	    <a name="qrydet.ColumnWidthPDF"></a><h4 class="title">Column Width (PDF)</h4><p>Indicates the width taken up by the column in PDF reports only. As
	      with other measurements, the value may be specified in cm, mm or if no
	      measurement is specified then points will be used.</p>

	      <p>If left blank the column will be automatically sized.</p><br />

	    <a name="qrydet.ColumnWidthHTML"></a><h4 class="title">Column Width (HTML)</h4><p>The width of the column as presented on HTML reports. This will be
	      used within the &quot;width&quot; CSS style of the &lt;TD&gt; tag that
	      contains the column value. To specify a value in pixels specify suffix of px, to specify a percentage value add a percent sign. The default is 100%</p>

      <p>Automatic sizing is used if this is left blank.</p><br />

    <a name="qrydet.column_title"></a><h4 class="title">Column Title</h4><p>The title of this column. This provides the text describing the
      column and is displayed in the column header section on the report. If
      this is left blank then the column name is used (but auto-capitalised with underscores removed).</p>

      <p>This also provides the default label for the column when it is
      displayed as a group header or a group trailer.</p><br />

    <a name="qrydet.group_header_label"></a><h4 class="title">Group Header Label</h4><p>The description label displayed next to the column value when
      displayed as a group header. By default this will revert to the
      <strong>Column Title</strong> value.</p><br />

    <a name="qrydet.group_header_label_xpos"></a><h4 class="title">Group Header Label Start (PDF)</h4><p>The horizontal start position of the group header label for this
      column. Defaults to the left margin.</p><br />

    <a name="qrydet.group_header_data_xpos"></a><h4 class="title">Group Header Value Start (PDF)</h4><p>The horizontal start position of the group header value for this
      column. Defaults to 250 points along from the label start.</p><br />

    <a name="qrydet.group_trailer_label"></a><h4 class="title">Group Trailer Label</h4><p>The group trailer label text for this column. Often a label is not
      required and can be removed by entering BLANK here. If required then it
      will default to the <strong>Column Title</strong> value if
      unspecified.</p><br /><hr />

  <a name="qrydet.PreSQLs"></a><h3 class="title">Query Details =&gt; Pre-SQLs</h3><p>Pre-SQLs are SQL statements that are run prior to the main report
    query and are used mainly to populate temporary tables to provide data to
    the main report query. They may be used to set a database mode. It is also
    possible to enter DELETE, UPDATE and transaction control statements here
    to allow reports to actually performed modifications to your
    database.</p>

      <p><strong class = "bold">Note that SQL entered may not be
      entered here when running in SAFE mode as the user could create SQL
      statements here that could delete and update data in the
      database. For instructions on how to turn on and off Safe Mode see the <a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a> section.</strong></p>


    <p>Pre-SQLs are executed in the order that they appear in the list. You
    can use the delete button to remove a Pre-SQLs or the move up and down
    buttons to change the execution order.</p>

    <a name="qrydet.PreSQLs.add"></a><h4 class="title">Add</h4><p>This Button creates a new Pre-SQL statement. To edit the query
      text click on the relevant Pre-SQL x button.</p><br />

    <a name="qrydet.PreSQLs.SQLText"></a><h4 class="title">Pre-SQL Text Entry</h4><p>The text should be a valid SQL statement acceptable by your
      database engine. It is not necessary to provide a closing
      semicolon.</p><br />

    <a name="qrydet.PreSQLs.linkprecrit"></a><h4 class="title">Linking in Criteria</h4><p>You can embed user criteria values into these statements by
      embedding references to criteria items in the same way as when embedding
      criteria references into the main report query or criteria queries. See <a href="../reportico/tutorial_reportico.qrydet.pkg.html#linkcrit">linkcrit</a> for more details.</p>

      <p>So, for example, the following Pre-SQLs would create a temporary
      table <strong>new_customers</strong>, fill it with customers relevant to a country and
      then remove all customers who have not ordered anything . This would
      then allow the main report to easily report on customers from the
      selected country who have not ordered anything by joining in this
      table:-</p>

      <table border = "1"><caption></caption><colgroup span = "2"/><col colwidth = "100"></col>

          <tbody><tr><td valign = "top"><strong>PreSql0</strong></td>

              <td><code>CREATE

    TEMPORARY TABLE new_customers ( customer_id int
    )</code></td></tr>

            <tr><td valign = "top"><strong>PreSql1</strong></td>

              <td><code><pre>
INSERT INTO new_customers 
SELECT customer_id 
FROM customers 
WHERE 1 = 1 
[AND country IN ( {country,VALUE})]
</pre></code></td></tr>

            <tr><td valign = "top"><strong>PreSql2</strong></td>

              <td><code><pre>
DELETE FROM new_customers 
WHERE customer_id NOT IN 
       ( SELECT customer_id FROM orders)
</pre></code></td></tr></tbody></table>

      <p></p><br /><hr /></span>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="33%" align="left" valign="top"><a href="../reportico/tutorial_reportico.customcode.pkg.html">Prev</a></td>
<td width="34%" align="center" valign="top"><a href=
"../reportico/tutorial_reportico.design.pkg.html">Up</a></td>
<td width="33%" align="right" valign="top"><a href=
"../reportico/tutorial_reportico.assign.pkg.html">Next</a></td>
</tr>

<tr>
<td width="33%" align="left" valign="top">Custom Source Code</td>
<td width="34%" align="center" valign="top">Report Design Mode</td>
<td width="33%" align="right" valign="top">The Assignments Menu</td>
</tr>
</table>
        <div class="credit">
		    <hr />
		    Documentation generated on Mon, 09 Jun 2014 18:10:07 +0100 by <a href="http://www.phpdoc.org">phpDocumentor 1.4.3</a>
	      </div>
      </td></tr></table>
    </td>
  </tr>
</table>

</body>
</html>